﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.ESS.MODEL;

namespace KPIS.GERP.ESS.DAL
{
    public class D_Absence
    {
        ConvertNullable.ConvertNullable cn = new ConvertNullable.ConvertNullable();

        public List<VM_Absence> AbsenceList(int? absence_seq, int? absence_type_seq, int? emp_seq, int? bgm_year, bool includeDeleted = false)
        {
            string strSQL;
            var VM_AbsenceList = new List<VM_Absence>();
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                /*  public int เลขที่ { get; set; }
                    public string รหัสคำขอ { get; set; }
                    public string ผู้ขอ { get; set; }
                    public string ประเภท { get; set; }
                    public DateTime วันที่เริ่ม { get; set; }
                    public string ช่วงเวลา { get; set; }
                    public string จำนวน { get; set; }
                    public string หมายเหตุ { get; set; }
                    public string สถานะ { get; set; }
                    public string รายละเอียด { get; set; }*/
                dbManager.Open();
                dbManager.CreateParameters(4);
                int i = 0;
                dbManager.AddParameters(i++, "@absence_seq", absence_seq);
                dbManager.AddParameters(i++, "@absence_type_seq", absence_type_seq);
                dbManager.AddParameters(i++, "@emp_seq", emp_seq);
                dbManager.AddParameters(i++, "@bgm_year", bgm_year);

                strSQL = @"SELECT 
                                ab.absence_seq AS 'absence_seq',
                                ab.absence_type_seq,
                                ab.ref_no,
                                ab.request_title,
                                CONCAT(
	                                 COALESCE(requestEmpPf.prefix_name, ''), ' ',
	                                 COALESCE(requestEmp.first_name, ''), ' ',
	                                 COALESCE(requestEmp.last_name, '')
                                ) AS 'full_name',
                                abType.absence_type_name AS 'absence_type_name',
                                ab.start_date AS 'start_date',
                                IIF(ab.start_time = 'M', 'ช่วงเช้า', 'ช่วงบ่าย') AS 'start_time',
                                ab.end_date AS 'end_date',
                                IIF(ab.end_time = 'M', 'ช่วงเช้า', 'ช่วงบ่าย') AS 'end_time',
                                ab.day_count AS 'day_count',
                                ab.remarks AS 'remarks',
                                ab.description AS 'description',
                                ab.bgm_year as 'bgm_year',
                                ab.record_status

                             FROM hrm_ps_absence ab
                                 LEFT JOIN hrm_ps_employees requestEmp ON ab.emp_seq = requestEmp.emp_seq
                                 LEFT JOIN mas_name_prefixes requestEmpPf ON requestEmp.name_prefix_seq = requestEmpPf.name_prefix_seq
                                 LEFT JOIN hrm_ps_absence_types abType ON abType.absence_type_seq = ab.absence_type_seq 
                            
                            Where 1 = 1
";
                if (!includeDeleted)
                {
                    strSQL += " and ab.record_status != 'D' ";
                }

                strSQL += (absence_seq.HasValue ? " and ab.absence_seq = @absence_seq" : "");
                strSQL += (absence_type_seq.HasValue ? " and ab.absence_type_seq = @absence_type_seq" : "");
                strSQL += (emp_seq.HasValue ? " and ab.emp_seq = @emp_seq" : "");
                strSQL += (bgm_year.HasValue ? " and ab.bgm_year = @bgm_year" : "");
                strSQL += " order by ab.absence_seq desc";

                DataTable resultDt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                foreach (DataRow dr in resultDt.Rows)
                {
                    VM_Absence eachAbsence = new VM_Absence();
                    eachAbsence.เลขที่ = Convert.ToInt32(dr["absence_seq"]);
                    eachAbsence.เลขที่ประเภทคำขอ = Convert.ToString(dr["absence_type_seq"]);
                    eachAbsence.รหัส = Convert.ToString(dr["ref_no"]);
                    eachAbsence.หัวข้อ = Convert.ToString(dr["request_title"]);
                    eachAbsence.ผู้ขอ = Convert.ToString(dr["full_name"]);
                    eachAbsence.ประเภท = Convert.ToString(dr["absence_type_name"]);
                    eachAbsence.วันที่เริ่ม = Convert.ToDateTime(dr["start_date"]).ToString("d MMM yyyy");
                    eachAbsence.ช่วงเวลาเริ่ม = Convert.ToString(dr["start_time"]);
                    eachAbsence.วันที่สิ้นสุด = Convert.ToDateTime(dr["end_date"]).ToString("d MMM yyyy");
                    eachAbsence.ช่วงเวลาสิ้นสุด = Convert.ToString(dr["end_time"]);
                    eachAbsence.จำนวน = Convert.ToString(dr["day_count"]);
                    eachAbsence.ปี = Convert.ToString(dr["bgm_year"]);
                    if (eachAbsence.จำนวน.Contains(".50"))
                    {
                        eachAbsence.จำนวน = eachAbsence.จำนวน.Replace(".50", "") + " วันครึ่ง";
                    } if (eachAbsence.จำนวน == "0 วันครึ่ง")
                    {
                        eachAbsence.จำนวน = "ครึ่งวัน";
                    }
                    else
                    {
                        eachAbsence.จำนวน = eachAbsence.จำนวน.Replace(".00", "") + " วัน";
                    }

                    eachAbsence.หมายเหตุ = Convert.ToString(dr["remarks"]);
                    eachAbsence.รายละเอียด = Convert.ToString(dr["description"]);

                    switch (Convert.ToString(dr["record_status"]))
                    {
                        case "A":
                            eachAbsence.สถานะ = "ผ่านการอนุมัติ";
                            break;
                        case "U":
                            eachAbsence.สถานะ = "มีผลใช้";
                            break;
                        case "W":
                            eachAbsence.สถานะ = "ยังไม่ส่งขออนุมัติ";
                            break;
                        case "P":
                            eachAbsence.สถานะ = "อยู่ระหว่างพิจารณา";
                            break;
                        case "D":
                            eachAbsence.สถานะ = "ถูกลบ";
                            break;
                        case "C":
                            eachAbsence.สถานะ = "ยกเลิก";
                            break;
                        case "R":
                            eachAbsence.สถานะ = "ถูกส่งกลับมาให้แก้ไข";
                            break;
                        case "J":
                            eachAbsence.สถานะ = "ตีกลับ";
                            break;
                    }

                    VM_AbsenceList.Add(eachAbsence);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return VM_AbsenceList;
        }

        public List<M_HrmAbsenceTypes> AbsenceTypeList(bool includeDeleted = false)
        {
            string strSQL;
            var absenceTypeList = new List<M_HrmAbsenceTypes>();
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(0);

                strSQL = @"SELECT absence_type_seq, absence_type_name FROM hrm_ps_absence_types ";
                if (!includeDeleted)
                {
                    strSQL += "WHERE record_status != 'D'";
                }

                DataTable resultDt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                foreach (DataRow dr in resultDt.Rows)
                {
                    M_HrmAbsenceTypes eachAbsenceType = new M_HrmAbsenceTypes();
                    eachAbsenceType.absence_type_seq = Convert.ToInt32(dr["absence_type_seq"]);
                    eachAbsenceType.absence_type_name = Convert.ToString(dr["absence_type_name"]);
                    absenceTypeList.Add(eachAbsenceType);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return absenceTypeList;
        }

        public List<M_HrmAbsenceNew> AbsenceNewList(int? absenceSeq = null, bool includeDeleted = false)
        {
            string strSQL;
            var absenceTypeList = new List<M_HrmAbsenceNew>();
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "absence_seq", absenceSeq);

                strSQL = @"SELECT
  abs.absence_seq,
  abs.ref_no, 
  abs.absence_type_seq,
abt.absence_type_name,
concat(COALESCE(npf.prefix_name,''),emp.first_name,' ',emp.last_name) as _emp_name,
  abs.bgm_year,
  abs.emp_seq,
  abs.start_date,
  abs.start_time,
  abs.end_date,
  abs.end_time,
  abs.day_count,
  abs.contact_address,
  abs.remarks,
  abs.record_status,
  abs.created_by,
  abs.created_when,
  abs.updated_by,
  abs.updated_when,
  abs.description,
  abs.request_title
FROM hrm_ps_absence abs left join hrm_ps_absence_types abt on abs.absence_type_seq = abt.absence_type_seq
left join hrm_ps_employees emp on emp.emp_seq = abs.emp_seq
left join mas_name_prefixes npf on emp.name_prefix_seq = npf.name_prefix_seq
where 1=1 
";

                if (!includeDeleted) strSQL += " and abs.record_status != 'D' "; 

                if (absenceSeq.HasValue) strSQL += " and abs.absence_seq = @absence_seq";

                DataTable resultDt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                foreach (DataRow dr in resultDt.Rows)
                {
                    var absence = new M_HrmAbsenceNew();
                    absence.absence_seq = Convert.ToInt32(dr["absence_seq"]);
                    absence.ref_no = Convert.ToString(dr["ref_no"]);
                    absence.absence_type_seq = Convert.ToInt32(dr["absence_type_seq"]);
                    absence._absence_type_name = Convert.ToString(dr["absence_type_name"]);
                    absence._emp_name = Convert.ToString(dr["_emp_name"]);
                    absence.bgm_year = cn.ConvertNullInt(dr["bgm_year"]);
                    absence.emp_seq = cn.ConvertNullInt(dr["emp_seq"]);
                    absence.start_date = cn.ConvertNullDateTime(dr["start_date"]);
                    absence.start_time = Convert.ToString(dr["start_time"]);
                    absence.end_date = cn.ConvertNullDateTime(dr["end_date"]);
                    absence.end_time = Convert.ToString(dr["end_time"]);
                    absence.day_count = cn.ConvertNullDouble(dr["day_count"]).HasValue ? Convert.ToDecimal(dr["day_count"]) : 0;
                    absence.contact_address = Convert.ToString(dr["contact_address"]);
                    absence.remarks = Convert.ToString(dr["remarks"]);
                    absence.record_status = Convert.ToString(dr["record_status"]);
                    absence.description = Convert.ToString(dr["description"]);
                    absence.request_title = Convert.ToString(dr["request_title"]);
                    absence.created_by = cn.ConvertNullInt(dr["created_by"]);
                    absence.created_when = cn.ConvertNullDateTime(dr["created_when"]);
                    absenceTypeList.Add(absence);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return absenceTypeList;
        }

        public void DeleteHrmPsAbsence(int absenceSeq, int? empSeq, string newStatus = "D")
        {
            string strSQL;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "absence_seq", absenceSeq);
                dbManager.AddParameters(1, "record_status", newStatus);
                dbManager.AddParameters(2, "updated_by", empSeq);

                strSQL = @"update hrm_ps_absence
set record_status = @record_status,
updated_by = @updated_by,
updated_when = GETDATE()
where absence_seq = @absence_seq;
";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string EmployeeFullName(int empSeq)
        {
            string strSQL;
            string result = "";
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "emp_seq", empSeq);

                strSQL = @"SELECT e.emp_seq, 
CONCAT(COALESCE(p.prefix_name,''),' ',COALESCE(e.first_name,''),' ',COALESCE(e.last_name,'')) AS full_name
FROM hrm_ps_employees e LEFT JOIN mas_name_prefixes p ON e.name_prefix_seq = p.name_prefix_seq
where e.emp_seq = @emp_seq;";
                DataTable resultDt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                DataRow dr;
                if (resultDt.Rows.Count > 0)
                {
                    dr = resultDt.Rows[0];
                    result = Convert.ToString(dr["full_name"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return result;
        }
    }
}
